package handlers

import (
	"database/sql"
	"fmt"
	"github.com/gin-gonic/gin"
	_ "github.com/go-sql-driver/mysql"
	"log"
	"net/http"
	"regexp"
	"strings"
	"time"
)

var db *sql.DB

// 初始化数据库连接
func initDB() {
	var err error
	dsn := "root:@tcp(127.0.0.1:3306)/vos3000"
	db, err = sql.Open("mysql", dsn)
	if err != nil {
		log.Fatalf("无法连接到数据库: %v", err)
	}
	err = db.Ping()
	if err != nil {
		log.Fatalf("无法连接到数据库: %v", err)
	}
	log.Println("成功连接到数据库")
}

func init() {
	initDB()
}

// 检查当前时间是否在限制范围内（早8点至晚8点）
func isWithinRestrictedTime() bool {
	now := time.Now()
	startTime := time.Date(now.Year(), now.Month(), now.Day(), 8, 0, 0, 0, now.Location())
	endTime := time.Date(now.Year(), now.Month(), now.Day(), 20, 0, 0, 0, now.Location())

	return now.After(startTime) && now.Before(endTime)
}

func BlindAreaHandler(c *gin.Context) {

	// 检查当前时间是否在限制范围内
	if isWithinRestrictedTime() {
		c.JSON(http.StatusOK, Response{0, "API 调用时间限制为早8点至晚8点之间不可用", nil})
		return
	}

	var req struct {
		SelectDate string `json:"data"`
	}
	if c.BindJSON(&req) != nil {
		c.JSON(http.StatusOK, Response{0, "未选日期", nil})
		return
	}

	// 验证日期格式
	if !regexp.MustCompile(`^\d{4}-\d{2}-\d{2}$`).MatchString(req.SelectDate) {
		c.JSON(http.StatusOK, Response{0, "日期格式不正确，请使用 YYYY-MM-DD 格式", nil})
		return
	}

	// 格式化表名
	tableName := fmt.Sprintf("e_cdr_%s", strings.ReplaceAll(req.SelectDate, "-", ""))

	// 创建临时表
	tempTableName := fmt.Sprintf("temp_%s", tableName)
	err := createTempTable(tableName, tempTableName)
	if err != nil {
		c.JSON(http.StatusOK, Response{0, "创建临时表失败", nil})
		return
	}
	defer dropTempTable(tempTableName) // 确保临时表在使用后被删除

	// 判断目标表是否存在 areacode 和 city 字段，若不存在则添加
	err = addFieldsIfNotExists(tempTableName)
	if err != nil {
		c.JSON(http.StatusOK, Response{0, "添加字段时出错", nil})
		return
	}

	// 更新 areacode 和 city 字段
	err = updateAreaCodeAndCity(tempTableName)
	if err != nil {
		c.JSON(http.StatusOK, Response{0, "更新字段时出错", nil})
		return
	}

	// 统计数据
	results, err := getStatistics(tempTableName)
	if err != nil {
		c.JSON(http.StatusOK, Response{0, "统计数据时出错", err})
		return
	}

	data := results
	c.JSON(http.StatusOK, Response{1, "成功", data})
}

// 创建临时表
func createTempTable(sourceTable, tempTable string) error {
	query := fmt.Sprintf(`
        CREATE TEMPORARY TABLE %s AS
        SELECT * FROM %s
    `, tempTable, sourceTable)
	_, err := db.Exec(query)
	if err != nil {
		return fmt.Errorf("创建临时表失败: %v", err)
	}
	log.Printf("已创建临时表: %s", tempTable)
	return nil
}

// 删除临时表
func dropTempTable(tempTable string) error {
	query := fmt.Sprintf("DROP TEMPORARY TABLE IF EXISTS %s", tempTable)
	_, err := db.Exec(query)
	if err != nil {
		return fmt.Errorf("删除临时表失败: %v", err)
	}
	log.Printf("已删除临时表: %s", tempTable)
	return nil
}

// 判断目标表是否存在 areacode 和 city 字段，若不存在则添加
func addFieldsIfNotExists(tableName string) error {
	// 检查 areacode 字段
	var count int
	err := db.QueryRow(fmt.Sprintf("SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'vos3000' AND TABLE_NAME = '%s' AND COLUMN_NAME = 'areacode'", tableName)).Scan(&count)
	if err != nil {
		return fmt.Errorf("检查 areacode 字段时出错: %v", err)
	}
	if count == 0 {
		_, err := db.Exec(fmt.Sprintf("ALTER TABLE %s ADD COLUMN areacode VARCHAR(255)", tableName))
		if err != nil {
			return fmt.Errorf("添加 areacode 字段时出错: %v", err)
		}
		log.Printf("已添加 areacode 字段到 %s 表", tableName)
	}

	// 检查 city 字段
	err = db.QueryRow(fmt.Sprintf("SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'vos3000' AND TABLE_NAME = '%s' AND COLUMN_NAME = 'city'", tableName)).Scan(&count)
	if err != nil {
		return fmt.Errorf("检查 city 字段时出错: %v", err)
	}
	if count == 0 {
		_, err := db.Exec(fmt.Sprintf("ALTER TABLE %s ADD COLUMN city VARCHAR(255)", tableName))
		if err != nil {
			return fmt.Errorf("添加 city 字段时出错: %v", err)
		}
		log.Printf("已添加 city 字段到 %s 表", tableName)
	}

	// 添加索引
	err = addIndexes(tableName)
	if err != nil {
		return fmt.Errorf("添加索引时出错: %v", err)
	}
	return nil
}

// 更新 areacode 和 city 字段
func updateAreaCodeAndCity(tableName string) error {
	query := fmt.Sprintf(`
        UPDATE %s
        JOIN e_mobilearea ON LEFT(RIGHT(calleee164, 11), 7) = e_mobilearea.mobileprefix
        SET %s.areacode = e_mobilearea.areacode, %s.city = e_mobilearea.city
    `, tableName, tableName, tableName)
	log.Printf("更新 areacode 和 city 字段 Query: %s ", query)
	_, err := db.Exec(query)
	if err != nil {
		return fmt.Errorf("更新 areacode 和 city 字段时出错: %v", err)
	}
	log.Printf("已更新 %s 表的 areacode 和 city 字段", tableName)
	return nil
}

// 统计数据
func getStatistics(tableName string) ([]map[string]interface{}, error) {
	query := fmt.Sprintf(`
        SELECT areacode, city, COUNT(*) as total_count, SUM(CASE WHEN agentfeetime > 0 THEN 1 ELSE 0 END) as fee_count
        FROM %s
        GROUP BY areacode, city
        ORDER BY fee_count ASC
    `, tableName)
	log.Printf("统计数据 Query: %s ", query)

	rows, err := db.Query(query)
	if err != nil {
		log.Printf("144 err: %v", err)
		return nil, fmt.Errorf("统计数据时出错: %v", err)
	}
	defer rows.Close()

	var results []map[string]interface{}
	var i = 1
	for rows.Next() {
		var areacode, city sql.NullString // 使用 sql.NullString 处理可能为 NULL 的字符串字段
		var totalCount, feeCount int

		err := rows.Scan(&areacode, &city, &totalCount, &feeCount)
		if err != nil {
			log.Printf("156 err: %v", err)
			return nil, fmt.Errorf("扫描统计结果时出错: %v", err)
		}

		// 处理 NULL 值
		areacodeValue := ""
		if areacode.Valid {
			areacodeValue = areacode.String
		}

		cityValue := ""
		if city.Valid {
			cityValue = city.String
		}

		result := map[string]interface{}{
			"id":        i,
			"areacode":  areacodeValue,
			"city":      cityValue,
			"calltotal": totalCount,
			"uptotal":   feeCount,
		}
		results = append(results, result)
		i++
	}

	if err = rows.Err(); err != nil {
		log.Printf("170 err: %v", err)
		return nil, fmt.Errorf("遍历结果集时出错: %v", err)
	}

	return results, nil
}

// 添加索引
func addIndexes(tableName string) error {
	// 检查 areacode 索引是否存在
	var indexExists int
	err := db.QueryRow(fmt.Sprintf("SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = 'vos3000' AND TABLE_NAME = '%s' AND INDEX_NAME = 'idx_areacode'", tableName)).Scan(&indexExists)
	if err != nil {
		return fmt.Errorf("检查 areacode 索引时出错: %v", err)
	}
	if indexExists == 0 {
		_, err := db.Exec(fmt.Sprintf("CREATE INDEX idx_areacode ON %s (areacode)", tableName))
		if err != nil {
			return fmt.Errorf("添加 areacode 索引时出错: %v", err)
		}
		log.Printf("已添加 areacode 索引到 %s 表", tableName)
	}

	// 检查 city 索引是否存在
	err = db.QueryRow(fmt.Sprintf("SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = 'vos3000' AND TABLE_NAME = '%s' AND INDEX_NAME = 'idx_city'", tableName)).Scan(&indexExists)
	if err != nil {
		return fmt.Errorf("检查 city 索引时出错: %v", err)
	}
	if indexExists == 0 {
		_, err := db.Exec(fmt.Sprintf("CREATE INDEX idx_city ON %s (city)", tableName))
		if err != nil {
			return fmt.Errorf("添加 city 索引时出错: %v", err)
		}
		log.Printf("已添加 city 索引到 %s 表", tableName)
	}

	// 检查 calleee164 索引是否存在
	err = db.QueryRow(fmt.Sprintf("SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = 'vos3000' AND TABLE_NAME = '%s' AND INDEX_NAME = 'idx_calleee164'", tableName)).Scan(&indexExists)
	if err != nil {
		return fmt.Errorf("检查 calleee164 索引时出错: %v", err)
	}
	if indexExists == 0 {
		_, err := db.Exec(fmt.Sprintf("CREATE INDEX idx_calleee164 ON %s (calleee164)", tableName))
		if err != nil {
			return fmt.Errorf("添加 calleee164 索引时出错: %v", err)
		}
		log.Printf("已添加 calleee164 索引到 %s 表", tableName)
	}

	return nil
}
